package cc.customer.web.util;
import cc.customer.bigEmport.damain.BigEmp;
import cc.customer.bigEmport.damain.EpRecords;
import cc.customer.bigEmport.damain.EprRecords;
import cc.customer.common.domain.ResponseBo;
import cc.customer.common.utils.DateUtil;
import cc.customer.common.utils.FebsUtil;
import cc.customer.common.utils.FileUtils;
import cc.customer.common.utils.Utils;
import cc.customer.common.utils.poi.POIUtils;
import cc.customer.utils.AutonCusInfo;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.util.*;

public class BigEmpReadExcel {
    // 构造方法
    public BigEmpReadExcel() {}
    //读取大名单初始数据
    public static  Map<String,Object> createExcel(String fileUrl, String batch, String bpResType, String uuid, String bpDretor) {
        List<BigEmp> list = new ArrayList<>();
        List<EprRecords> errorList = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        
        try {
            File file = new File(fileUrl);
            Workbook rwb= new XSSFWorkbook(new FileInputStream(file));
            Sheet sheet = rwb.getSheetAt(0);
            int rows = sheet.getLastRowNum();//获取所有得行
            if (rows<=0){
                return map;
            }
            for (int i = 1; i <= rows; i++) {
                BigEmp bigEmp = null;
                EprRecords  records = null;
                Row row = sheet.getRow(i);
                records = checkAndDownLoadError(row,batch);

                if(null!=records){
                    errorList.add(records);
                }else{
                    bigEmp = new BigEmp();
                    //获取客户名称
                    Cell bpName = row.getCell(0);
                    if (bpName!=null){
                        bigEmp.setBpName(bpName.getStringCellValue().trim());
                    }else{
                        continue;
                    }
                    //获取性别
                    Cell bpSex = row.getCell(1);
                    if (bpSex!=null){
                        bigEmp.setBpSex(bpSex.getStringCellValue().trim());
                    }
                    //获取手机号
                    Cell bpPhone = row.getCell(2);
                    if (bpPhone!=null){
                        String opone = POIUtils.getCellValue(bpPhone);
                        bigEmp.setBpMobile(opone.trim());
                    }else{
                        continue;
                    }
                    //来源渠道
                    Cell bpSourceProxy = row.getCell(3);
                    if (bpSourceProxy!=null){
                        bigEmp.setBpSourChanel(bpSourceProxy.getStringCellValue().trim());
                    }
                    //初始意向项目
                    Cell bpInteProject =  row.getCell(4);
                    if (bpInteProject!=null){
                        bigEmp.setBpInteProject(bpInteProject.getStringCellValue().trim());
                    }
                    //意向项目
                    Cell BpInteAipro = row.getCell(5);
                    if(BpInteAipro!=null){
                        bigEmp.setBpInteAipro(BpInteAipro.getStringCellValue().trim());
                    }
                    //意向等级
                    Cell bpGroud = row.getCell(6);
                    if (bpGroud!=null){
                        bigEmp.setBpGroud(bpGroud.getStringCellValue().trim());
                    }
                    //QQ号
                    Cell bpQQ = row.getCell(7);
                    if (bpQQ!=null){
                        String bpQQq = POIUtils.getCellValue(bpQQ);
                        bigEmp.setBpQq(bpQQq);
                    }
                    //地区
                    Cell bpCity = row.getCell(8);
                    if(bpCity!=null){
                        bigEmp.setBpCity(bpCity.getStringCellValue().trim());
                    }
                    //片区
                    Cell bpZone = row.getCell(9);
                    if(bpZone!=null){
                        bigEmp.setBpZone(bpZone.getStringCellValue().trim());
                    }
                    //高校名称
                    Cell bpCollges = row.getCell(10);
                    if (bpCollges!=null){
                        bigEmp.setBpColleges(bpCollges.getStringCellValue().trim());
                    }
                    //学院
                    Cell bpAcadmy = row.getCell(11);
                    if(bpAcadmy!=null){
                        bigEmp.setBpAcademy(bpAcadmy.getStringCellValue().trim());
                    }
                    //专业
                    Cell bpMajor = row.getCell(12);
                    if(bpMajor!=null){
                        bigEmp.setBpMajor(bpMajor.getStringCellValue().trim());
                    }
                    //入学年份
                    Cell bpYear = row.getCell(13);
                    if(bpYear!=null){
                        String bpYearStr =   POIUtils.getCellValue(bpYear);
                        bigEmp.setBpYear(bpYearStr);
                    }
                    //学历层次
                    Cell bpEducation = row.getCell(14);
                    if(bpEducation!=null){
                        bigEmp.setBpEducation(AutonCusInfo.parseBigExmEducation(bpEducation.getStringCellValue().trim()));
                    }
                    //所属批次
                    bigEmp.setBpBatch(batch);
                    bigEmp.setBpDirector(bpDretor);
                    bigEmp.setBpEpNum("0");
                    bigEmp.setBpUuid(uuid);
                    bigEmp.setBpRUuid(Utils.getUUID());
                    bigEmp.setBpStage("1");
                    bigEmp.setBpCreatetime(DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss"));
                    //资源类型
                    bigEmp.setBpResType(bpResType);
                    bigEmp.setBpIsdelete("0");
                    list.add(bigEmp);
                }
            }
            map.put("list",list);
            map.put("errorList",errorList);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    //大资源初始名单的校验数据信息
    public static ResponseBo checkExcel(String fileUrl) {
        Boolean isCheck = false;
        int num = 0;
        String  retuanMsg = "";
        try {
            File file = new File(fileUrl);
            Workbook rwb= new XSSFWorkbook(new FileInputStream(file));
            Sheet sheet = rwb.getSheetAt(0);
            int rows = sheet.getLastRowNum();//获取所有得行
            if (rows<=0){
                return ResponseBo.error("导入数据为空");
            }
            Set<String> allPhone = new HashSet<>();
            for (int i = 1; i <= rows; i++) {
                num++;
                BigEmp bigEmp = new BigEmp();
                Row row = sheet.getRow(i);
                //获取客户名称
                Cell bpName = row.getCell(0);
                if (bpName!=null){
                    String name =  bpName.getStringCellValue();
                    if (!StringUtils.isEmpty(name)){
                        bigEmp.setBpName(name);
                    }
                }else{
                    isCheck = true;
                    retuanMsg = "客户名称不能为空";
                    break;
                }
                //获取手机号
                Cell bpPhone = row.getCell(2);
                if (bpPhone!=null){
                    String opone =   POIUtils.getCellValue(bpPhone);
                    if (!StringUtils.isEmpty(opone)&& FebsUtil.isPhoneNo(opone)){
                        bigEmp.setBpMobile(opone);
                    }else{
                        isCheck = true;
                        retuanMsg = "手机号不能为空或者手机号格式不对";
                        break;
                    }
                    //验证是否存在存在相同的手机号数据
                    allPhone.add(opone);
                }else{
                    isCheck = true;
                    retuanMsg = "手机号不能为空或者手机号格式不对";
                    break;
                }
            }
            //验证是不是存在不是手机号或者 手机号不正确的数据
            if (isCheck){
                return ResponseBo.error("验证失败：第 "+num+" 条数据不正确,原因:"+retuanMsg);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return ResponseBo.ok("验证成功");
    }


    /**导入时错误信息形成excl返回*/
    private static EprRecords checkAndDownLoadError(Row row,String batch) {
        Boolean isCheck = false;
        EprRecords bigEmp = null;
        try {
            String reson = "";
            //获取客户名称
            Cell bpName = row.getCell(0);
            if (bpName==null||StringUtils.isEmpty(bpName+"")){
                reson = "客户名称为空";
                isCheck = true;
            }
            //获取手机号
            Cell bpPhone = row.getCell(2);
            if (bpPhone!=null){
                String opone = POIUtils.getCellValue(bpPhone);
                if (!StringUtils.isEmpty(opone)&& FebsUtil.isPhoneNo(opone)){
                    //bigEmp.setBpMobile(opone);
                }else{
                    reson = "客户手机号不符合规则";
                    isCheck = true;
                }
            }else{
                reson = "客户手机号为空";
                isCheck = true;
            }

            //验证是不是存在不是手机号或者 手机号不正确的数据
            if (isCheck){
                bigEmp = new EprRecords();
                bigEmp.setcEprBatch(batch);
                //获取客户名称
                if(bpName != null){
                    bigEmp.setcEprUser(bpName.getStringCellValue().trim());
                }

                //获取性别
                Cell bpSex = row.getCell(1);
                if (bpSex!=null){
                    bigEmp.setcEprSex(bpSex.getStringCellValue().trim());
                }
                //获取手机号
                String opone = POIUtils.getCellValue(bpPhone);
                bigEmp.setcEprPhone(opone.trim());
                //来源渠道
                Cell bpSourceProxy = row.getCell(3);
                if (bpSourceProxy!=null){
                    bigEmp.setcEprQudao(bpSourceProxy.getStringCellValue().trim());
                }
                //初始意向项目
                Cell bpInteProject =  row.getCell(4);
                if (bpInteProject!=null){
                    bigEmp.setcEprChu(bpInteProject.getStringCellValue().trim());
                }
                //意向项目
                Cell BpInteAipro = row.getCell(5);
                if(BpInteAipro!=null){
                    bigEmp.setcEprYi(BpInteAipro.getStringCellValue().trim());
                }
                //意向等级
                Cell bpGroud = row.getCell(6);
                if (bpGroud!=null){
                    bigEmp.setcEprDeng(bpGroud.getStringCellValue().trim());
                }
                //QQ号
                Cell bpQQ = row.getCell(7);
                if (bpQQ!=null){
                    String bpQQq =   POIUtils.getCellValue(bpQQ);
                    bigEmp.setcEprQQ(bpQQq);
                }
                //地区
                Cell bpCity = row.getCell(8);
                if(bpCity!=null){
                    bigEmp.setcEprCity(bpCity.getStringCellValue().trim());
                }
                //片区
                Cell bpZone = row.getCell(9);
                if(bpZone!=null){
                    bigEmp.setcEprZone(bpZone.getStringCellValue().trim());
                }
                //高校名称
                Cell bpCollges = row.getCell(10);
                if (bpCollges!=null){
                    bigEmp.setcEprCollage(bpCollges.getStringCellValue().trim());
                }
                //学院
                Cell bpAcadmy = row.getCell(11);
                if(bpAcadmy!=null){
                    bigEmp.setcEprPace(bpAcadmy.getStringCellValue().trim());
                }
                //专业
                Cell bpMajor = row.getCell(12);
                if(bpMajor!=null){
                    bigEmp.setcEprZhuan(bpMajor.getStringCellValue().trim());
                }
                //入学年份
                Cell bpYear = row.getCell(13);
                if(bpYear!=null){
                    String bpYearStr =   POIUtils.getCellValue(bpYear);
                    bigEmp.setcEprYear(bpYearStr);
                }
                //学历层次
                Cell bpEducation = row.getCell(14);
                if(bpEducation!=null){
                    bigEmp.setcEprEdu(bpEducation.getStringCellValue().trim());
                }
                bigEmp.setcEprReson(reson);
                bigEmp.setcEprTime(new Date());
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return bigEmp;
    }


    /**
     * 读取大资源意向名单的数据
     * **/
    public static List<BigEmp> createExcelExport(String fileUrl) {
        List<BigEmp> list = new ArrayList<>();
        try {
            File file = new File(fileUrl);
            Workbook rwb= new XSSFWorkbook(new FileInputStream(file));
            Sheet sheet = rwb.getSheetAt(0);
            int rows = sheet.getLastRowNum();//获取所有得行
            for (int i = 1; i <= rows; i++) {
                BigEmp bigEmp = new BigEmp();
                Row row = sheet.getRow(i);
                //获取手机号
                Cell bpPhone = row.getCell(2);
                if (bpPhone!=null){
                    String opone =   POIUtils.getCellValue(bpPhone);
                    bigEmp.setBpMobile(opone);
                }else{
                    continue;
                }
                //意向等级
                Cell bpGroup = row.getCell(6);
                if(bpGroup!=null){
                    bigEmp.setBpGroud(bpGroup.getStringCellValue().trim());
                }
                //意向项目
                Cell bpArewpi = row.getCell(5);
                if(bpArewpi!=null){
                    bigEmp.setBpInteAipro(","+bpArewpi.getStringCellValue().trim());
                }
                list.add(bigEmp);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**大资源意向名单的校验规则**/
    public static ResponseBo checkExcelExport(String fileUrlExport) {
        Boolean isCheckExport = false;
        int num = 0;
        String msg = "验证成功";
        try {
            File file = new File(fileUrlExport);
            Workbook rwb= new XSSFWorkbook(new FileInputStream(file));
            Sheet sheet = rwb.getSheetAt(0);
            int rows = sheet.getLastRowNum();//获取所有得行
            if (rows<=0){
                return ResponseBo.error("导入数据为空");
            }
            Set<String> allPhone = new HashSet<>();
            for (int i = 1; i <= rows; i++) {
                num++;
                BigEmp bigEmp = new BigEmp();
                Row row = sheet.getRow(i);
                /**获取用户的意向等级***/
                Cell Group = row.getCell(6);
                if (Group!=null){
                    String GroupName  = Group.getStringCellValue();
                    if (!StringUtils.isEmpty(GroupName)){
                        bigEmp.setBpGroud(GroupName);
                    }else{
                        isCheckExport = true;
                        msg = "意向等级为空";
                        break;
                    }
                }else{
                    isCheckExport = true;
                    msg = "意向等级为空";
                    break;
                }
                //获取客户名称 客户名称不能为空
                Cell bpName = row.getCell(0);
                if (bpName == null){
                    isCheckExport = true;
                    msg = "客户名称为空";
                    break;
                }
                String customerName =  bpName.getStringCellValue();
                if (!StringUtils.isEmpty(customerName)){
                    bigEmp.setBpName(customerName);
                }else{
                    isCheckExport = true;
                    msg = "客户名称为空";
                    break;
                }
                //获取手机号
                Cell bpPhone = row.getCell(2);
                String opone =   POIUtils.getCellValue(bpPhone);
                if (!StringUtils.isEmpty(opone)&& FebsUtil.isPhoneNo(opone)){
                    bigEmp.setBpMobile(opone);
                }else{
                    isCheckExport = true;
                    msg = "手机号为空";
                    break;
                }
                //验证是否存在存在相同的手机号数据
                allPhone.add(opone);
            }
            //验证是不是存在不是手机号或者 手机号不正确的数据
            if (isCheckExport){
                return ResponseBo.error("验证失败：第 "+num+" 条数据不正确,原因:"+msg);
            }
            //验证这个批次 有相同的数据的吗
            if (rows!= allPhone.size()){
                return ResponseBo.error("验证失败：批次中 存在手机号相同的数据");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return ResponseBo.ok("验证成功");
    }



}
